package org.cypress.rtpack.utils;

import org.cypress.rtpack.controller.RtpackConfigController;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Dbsvr {
    // 私有构造方法防止实例化
    private Dbsvr() {
        throw new AssertionError("不允许实例化工具类！");
    }
    private static final Logger logger = LoggerFactory.getLogger(Dbsvr.class);
    private static final String JDBC_URL_PREFIX = "jdbc:sqlite:";

    /**
     * 根据index从数据库查询单个实体
     * @param dbName 数据库名称（不含扩展名）
     * @param tableName 表名
     * @param entityClass 实体类类型
     * @param keyV 要查询的索引值
     * @param <T> 泛型类型
     * @return 查询到的实体对象，未找到返回null
     */
    public static <T> T getEntity(String dbName, String tableName, Class<T> entityClass, int keyV,String keyColNam) {
        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        String sqlCmd;
        if (keyColNam.equals("index")){  //keyColNm是关键字，多一对引号
            sqlCmd = String.format("SELECT * FROM %s WHERE \"%s\" = ?", tableName,keyColNam);
        }
        else{
            sqlCmd = String.format("SELECT * FROM %s WHERE %s = ?", tableName,keyColNam);
        }


        try (Connection connection = DriverManager.getConnection(jdbcUrl);
             PreparedStatement statement = connection.prepareStatement(sqlCmd)) {

            // 设置查询参数
            statement.setInt(1, keyV);

            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    // 创建实体类实例
                    T entity = entityClass.getDeclaredConstructor().newInstance();

                    // 获取结果集元数据
                    ResultSetMetaData metaData = resultSet.getMetaData();
                    int columnCount = metaData.getColumnCount();

                    // 填充实体属性
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        Object value = resultSet.getObject(i);

                        // 特殊处理enabled字段
                        if ("enabled".equalsIgnoreCase(columnName) && entity instanceof org.cypress.rtpack.VO.SoeIp) {
                            value = resultSet.getInt(i) == 1;
                        }
                        // 特殊处理enabled字段
                        if ("soe_enabled".equalsIgnoreCase(columnName) && entity instanceof org.cypress.rtpack.VO.SoePt) {
                            value = resultSet.getInt(i) == 1;
                        }

                        // 使用反射设置属性
                        try {
                            String setterName = findSetterName(columnName);   //  "soe_id"   -->     "setSoeId"

                            // 获取字段类型以正确处理参数类型
                            Field field = entityClass.getDeclaredField(toCamelCase(columnName));
                            Class<?> fieldType = field.getType();

                            // 类型转换
                            if (value instanceof Number) {
                                if (fieldType == int.class || fieldType == Integer.class) {
                                    value = ((Number)value).intValue();
                                } else if (fieldType == long.class || fieldType == Long.class) {
                                    value = ((Number)value).longValue();
                                }
                            }

                            entityClass.getMethod(setterName, fieldType)
                                    .invoke(entity, value);
                        } catch (NoSuchMethodException | NoSuchFieldException e) {
                            logger.warn("Field '{}' not accessible in class {}",
                                    columnName, entityClass.getName());
                        }
                    }
                    return entity;
                }
                return null; // 未找到记录
            }
        } catch (Exception e) {
            throw new RuntimeException("数据库查询失败", e);
        }
    }

    /**
     * 根据index从数据库查询单个实体
     * @param dbName 数据库名称（不含扩展名）
     * @param tableName 表名
     * @param entityClass 实体类类型
     * @param keyV 要查询的索引值
     * @param <T> 泛型类型
     * @return 查询到的实体对象，未找到返回null
     */
    public static <T> T getEntity(String dbName, String tableName, Class<T> entityClass, String keyV,String keyColNam) {
        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        String sqlCmd;
        if (keyColNam.equals("index")){  //keyColNm是关键字，多一对引号
            sqlCmd = String.format("SELECT * FROM %s WHERE \"%s\" = ?", tableName,keyColNam);
        }
        else{
            sqlCmd = String.format("SELECT * FROM %s WHERE %s = ?", tableName,keyColNam);
        }


        try (Connection connection = DriverManager.getConnection(jdbcUrl);
             PreparedStatement statement = connection.prepareStatement(sqlCmd)) {

            // 设置查询参数
            statement.setString(1, keyV);

            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    // 创建实体类实例
                    T entity = entityClass.getDeclaredConstructor().newInstance();

                    // 获取结果集元数据
                    ResultSetMetaData metaData = resultSet.getMetaData();
                    int columnCount = metaData.getColumnCount();

                    // 填充实体属性
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        Object value = resultSet.getObject(i);

                        // 特殊处理enabled字段
                        if ("enabled".equalsIgnoreCase(columnName) && entity instanceof org.cypress.rtpack.VO.SoeIp) {
                            value = resultSet.getInt(i) == 1;
                        }
                        // 特殊处理enabled字段
                        if ("soe_enabled".equalsIgnoreCase(columnName) && entity instanceof org.cypress.rtpack.VO.SoePt) {
                            value = resultSet.getInt(i) == 1;
                        }

                        // 使用反射设置属性
                        try {
                            String setterName = findSetterName(columnName);   //  "soe_id"   -->     "setSoeId"

                            // 获取字段类型以正确处理参数类型
                            Field field = entityClass.getDeclaredField(toCamelCase(columnName));
                            Class<?> fieldType = field.getType();

                            // 类型转换
                            if (value instanceof Number) {
                                if (fieldType == int.class || fieldType == Integer.class) {
                                    value = ((Number)value).intValue();
                                } else if (fieldType == long.class || fieldType == Long.class) {
                                    value = ((Number)value).longValue();
                                }
                            }

                            entityClass.getMethod(setterName, fieldType)
                                    .invoke(entity, value);
                        } catch (NoSuchMethodException | NoSuchFieldException e) {
                            logger.warn("Field '{}' not accessible in class {}",
                                    columnName, entityClass.getName());
                        }
                    }
                    return entity;
                }
                return null; // 未找到记录
            }
        } catch (Exception e) {
            throw new RuntimeException("数据库查询失败", e);
        }
    }

    /**
     * 获取数据库表中的数据列表
     *
     * @param dbName      数据库名称（不含扩展名）
     * @param tableName   表名
     * @param entityClass 实体类类型
     * @param <T>         泛型类型
     * @return 包含查询结果的列表
     */
    public static <T> List<T> getList(String dbName, String tableName, Class<T> entityClass) {
        List<T> resultList = new ArrayList<>();
        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        String sqlCmd = String.format("SELECT * FROM %s",tableName);
        try (Connection connection = DriverManager.getConnection(jdbcUrl);
             Statement statement = connection.createStatement();

             ResultSet resultSet = statement.executeQuery(sqlCmd)) {

            // 获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                // 创建实体类实例
                T entity = entityClass.getDeclaredConstructor().newInstance();

                // 遍历所有列
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = resultSet.getObject(i);

                    // 获取字段类型
                    Class<?> fieldType = entityClass.getDeclaredField(toCamelCase(columnName)).getType();

                    // 特殊处理enabled字段的类型转换
                    if ("enabled".equalsIgnoreCase(columnName) && entity instanceof org.cypress.rtpack.VO.SoeIp) {
                        value = resultSet.getInt(i) == 1;
                        fieldType = boolean.class;
                    }
                    if ("soe_enabled".equalsIgnoreCase(columnName) && entity instanceof org.cypress.rtpack.VO.SoePt) {
                        value = resultSet.getInt(i) == 1;
                        fieldType = boolean.class;
                    }

                    // 类型转换
                    if (value instanceof Number) {
                        if (fieldType == int.class) {
                            value = ((Number)value).intValue();
                        } else if (fieldType == long.class) {
                            value = ((Number)value).longValue();
                        }
                        // 其他数字类型...
                    }
                    // 使用setter方法设置属性
                    try {
                        String setterName = findSetterName(columnName);
                        entityClass.getMethod(setterName, fieldType)
                                .invoke(entity, value);
                    } catch (NoSuchMethodException e) {
                        logger.warn("No setter method for field '{}' in class {}",
                                columnName, entityClass.getName());
                    }
                }
                resultList.add(entity);
            }
        } catch (Exception e) {
            System.err.println("Database error: " + e.getMessage());
            e.printStackTrace();
        }
        return resultList;
    }

    /**
     * 根据实体类型和index更新数据库记录（支持多种实体）
     * @param dbName 数据库名称
     * @param tableName 表名
     * @param entity 要更新的实体对象
     * @param <T> 实体类型
     * @return 更新是否成功
     */
    public static <T> boolean updateEntity(String dbName, String tableName, String keyColNam, T entity) {
        if (entity == null) {
            logger.warn("Entity cannot be null");
            return false;
        }

        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        Class<?> entityClass = entity.getClass();
        try (Connection connection = DriverManager.getConnection(jdbcUrl)) {
            // 1. 获取实体类信息

            Field[] fields = entityClass.getDeclaredFields();

            // 2. 构建动态SQL
            StringBuilder sqlBuilder = new StringBuilder("UPDATE ")
                    .append(tableName)
                    .append(" SET ");

            List<Object> params = new ArrayList<>();
            Object indexValue = null;

            // 3. 遍历字段构建SET子句
            boolean firstField = true;
            for (Field field : fields) {
                field.setAccessible(true);
                String fieldName = field.getName();
                String underlineName = camelToUnderline(fieldName);
                Object value = field.get(entity);

                // 跳过index字段（作为WHERE条件）
                if (keyColNam.equals(underlineName)) {
                    indexValue = value;
                    continue;
                }

                if (!firstField) {
                    sqlBuilder.append(", ");
                }
                sqlBuilder.append(underlineName).append(" = ?");
                params.add(value);
                firstField = false;
            }

            // 4. 添加WHERE条件
            if (indexValue == null) {
                throw new IllegalArgumentException("Entity must have 'index' field");
            }
            if (keyColNam.equals("index")) {
                sqlBuilder.append(String.format(" WHERE \"%s\" = ?",keyColNam));
            }
            else{
                sqlBuilder.append(String.format(" WHERE %s = ?",keyColNam));
            }
            params.add(indexValue);

            String sql = sqlBuilder.toString();

            // 5. 执行更新
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                // 设置参数
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);

                    // 特殊处理boolean类型
                    if (param instanceof Boolean) {
                        stmt.setInt(i + 1, (Boolean)param ? 1 : 0);
                    } else {
                        stmt.setObject(i + 1, param);
                    }
                }

                int rows = stmt.executeUpdate();
                return rows > 0;
            }
        } catch (Exception e) {
            logger.error("Update failed for {}: {}", entityClass.getSimpleName(), e.getMessage(), e);
            return false;
        }
    }

    /**
     * 物理删除表中的所有记录（清空表）
     * @param dbName 数据库名称（不含扩展名）
     * @param tableName 表名
     * @return 删除是否成功
     */
    public static boolean dropTable(String dbName, String tableName) {
        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        String sqlCmd = String.format("DELETE FROM %s", tableName);


        try (Connection connection = DriverManager.getConnection(jdbcUrl);
             Statement statement = connection.createStatement()) {

            int affectedRows = statement.executeUpdate(sqlCmd);

            return true;
        } catch (SQLException e) {
            logger.error("Failed to drop table {}: {}", tableName, e.getMessage(), e);
            return false;
        }
    }

    /**
     * 插入单个实体到数据库表
     * @param dbName 数据库名称（不含扩展名）
     * @param tableName 表名
     * @param entity 要插入的实体对象
     * @param <T> 实体类型
     * @return 插入是否成功
     */
    public static <T> boolean insertEntity(String dbName, String tableName, T entity) {
        if (entity == null) {
            logger.warn("Entity cannot be null");
            return false;
        }

        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        Class<?> entityClass = entity.getClass();

        try (Connection connection = DriverManager.getConnection(jdbcUrl)) {
            // 1. 获取所有非transient字段
            List<Field> fields = new ArrayList<>();
            for (Field field : entityClass.getDeclaredFields()) {
                if (!java.lang.reflect.Modifier.isTransient(field.getModifiers())) {
                    fields.add(field);
                }
            }

            // 2. 构建动态SQL
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ")
                    .append(tableName)
                    .append(" (");

            // 构建列名部分
            boolean firstField = true;
            for (Field field : fields) {
                if (!firstField) {
                    sqlBuilder.append(", ");
                }
                sqlBuilder.append(camelToUnderline(field.getName()));
                firstField = false;
            }

            sqlBuilder.append(") VALUES (");

            // 构建值占位符部分
            firstField = true;
            for (int i = 0; i < fields.size(); i++) {
                if (!firstField) {
                    sqlBuilder.append(", ");
                }
                sqlBuilder.append("?");
                firstField = false;
            }
            sqlBuilder.append(")");

            String sql = sqlBuilder.toString();

            // 3. 执行插入
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                // 设置参数
                for (int i = 0; i < fields.size(); i++) {
                    Field field = fields.get(i);
                    field.setAccessible(true);
                    Object value = field.get(entity);

                    // 特殊处理boolean类型
                    if (value instanceof Boolean) {
                        stmt.setInt(i + 1, (Boolean)value ? 1 : 0);
                    } else {
                        stmt.setObject(i + 1, value);
                    }
                }

                int affectedRows = stmt.executeUpdate();
                return affectedRows > 0;
            }
        } catch (Exception e) {
            logger.error("Insert failed for {}: {}", entityClass.getSimpleName(), e.getMessage(), e);
            return false;
        }
    }

    /**
     * 批量插入实体列表
     * @param dbName 数据库名称（不含扩展名）
     * @param tableName 表名
     * @param list 要插入的实体列表
     * @param <T> 实体类型
     * @return 插入是否成功
     */
    public static <T> boolean insertList(String dbName, String tableName, List<T> list) {
        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        Class<?> entityClass = list.get(0).getClass();

        try (Connection connection = DriverManager.getConnection(jdbcUrl)) {
            connection.setAutoCommit(false);

            // 2. 构建动态SQL
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ")
                    .append(tableName)
                    .append(" (");

            // 获取所有字段名（排除transient字段）
            List<Field> fields = new ArrayList<>();
            for (Field field : entityClass.getDeclaredFields()) {
                if (!java.lang.reflect.Modifier.isTransient(field.getModifiers())) {
                    fields.add(field);
                }
            }

            // 构建列名部分
            boolean firstField = true;
            for (Field field : fields) {
                if (!firstField) {
                    sqlBuilder.append(", ");
                }
                sqlBuilder.append(camelToUnderline(field.getName()));
                firstField = false;
            }

            sqlBuilder.append(") VALUES (");

            // 构建值占位符部分
            firstField = true;
            for (int i = 0; i < fields.size(); i++) {
                if (!firstField) {
                    sqlBuilder.append(", ");
                }
                sqlBuilder.append("?");
                firstField = false;
            }
            sqlBuilder.append(")");

            String sql = sqlBuilder.toString();

            // 3. 批量插入
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                for (T entity : list) {
                    // 设置参数
                    for (int i = 0; i < fields.size(); i++) {
                        Field field = fields.get(i);
                        field.setAccessible(true);
                        Object value = field.get(entity);

                        // 特殊处理boolean类型
                        if (value instanceof Boolean) {
                            stmt.setInt(i + 1, (Boolean)value ? 1 : 0);
                        } else {
                            stmt.setObject(i + 1, value);
                        }
                    }
                    stmt.addBatch();
                }

                int[] results = stmt.executeBatch();
                connection.commit();

                // 检查执行结果
                for (int result : results) {
                    if (result == Statement.EXECUTE_FAILED) {
                        logger.error("Batch insert failed for some records");
                        return false;
                    }
                }

                return true;
            }
        } catch (Exception e) {
            logger.error("Batch insert failed: {}", e.getMessage(), e);
            return false;
        }
    }

    /**
     * 判断实体是否已存在（所有字段值完全匹配）
     * 优化版：只要找到一条匹配记录就立即返回，不检查全部数据
     * @param dbName 数据库名称（不含扩展名）
     * @param tableName 表名
     * @param entity 要检查的实体对象
     * @param <T> 实体类型
     * @return 如果存在完全匹配的记录返回true，否则返回false
     */
    public static <T> boolean isEntityExists(String dbName, String tableName, T entity) {
        if (entity == null) {
            logger.warn("Entity cannot be null");
            return false;
        }

        String jdbcUrl = JDBC_URL_PREFIX + dbName + ".db";
        Class<?> entityClass = entity.getClass();

        try (Connection connection = DriverManager.getConnection(jdbcUrl)) {
            // 1. 获取所有非transient字段
            List<Field> fields = new ArrayList<>();
            for (Field field : entityClass.getDeclaredFields()) {
                if (!java.lang.reflect.Modifier.isTransient(field.getModifiers())) {
                    fields.add(field);
                }
            }

            // 2. 构建动态SQL - 使用LIMIT 1优化查询
            StringBuilder sqlBuilder = new StringBuilder("SELECT 1 FROM ")
                    .append(tableName)
                    .append(" WHERE ");

            List<Object> params = new ArrayList<>();
            boolean firstField = true;

            // 3. 遍历字段构建WHERE条件
            for (Field field : fields) {
                field.setAccessible(true);
                String fieldName = field.getName();
                String columnName = camelToUnderline(fieldName);
                Object value = field.get(entity);

                if (!firstField) {
                    sqlBuilder.append(" AND ");
                }

                if (columnName.equals("\"index\"")) {
                    sqlBuilder.append(columnName).append(" = ?");
                } else {
                    sqlBuilder.append(columnName).append(" = ?");
                }

                params.add(value);
                firstField = false;
            }

            // 添加LIMIT 1优化
            sqlBuilder.append(" LIMIT 1");
            String sql = sqlBuilder.toString();

            // 4. 执行查询
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                // 设置参数
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);

                    // 特殊处理boolean类型
                    if (param instanceof Boolean) {
                        stmt.setInt(i + 1, (Boolean)param ? 1 : 0);
                    } else {
                        stmt.setObject(i + 1, param);
                    }
                }

                try (ResultSet rs = stmt.executeQuery()) {
                    return rs.next(); // 有结果就返回true
                }
            }
        } catch (Exception e) {
            logger.error("Check entity existence failed for {}: {}", entityClass.getSimpleName(), e.getMessage(), e);
            return false;
        }
    }

    /**
     * 将数据库列名转换为Java小驼峰命名变量名
     * @param columnName 数据库列名（如：soe_id, channel_name）
     * @return Java变量名（如：soeId, channelName）
     */
    public static String toCamelCase(String columnName) {
        if (columnName == null || columnName.isEmpty()) {
            return columnName;
        }

        // 处理全大写或包含下划线的列名
        StringBuilder result = new StringBuilder();
        boolean nextUpper = false;

        for (int i = 0; i < columnName.length(); i++) {
            char currentChar = columnName.charAt(i);

            if (currentChar == '_') {
                nextUpper = true;
            } else {
                if (nextUpper) {
                    result.append(Character.toUpperCase(currentChar));
                    nextUpper = false;
                } else {
                    // 首字母小写，其余保持原样
                    if (i == 0) {
                        result.append(Character.toLowerCase(currentChar));
                    } else {
                        result.append(currentChar);
                    }
                }
            }
        }

        return result.toString();
    }

    /**
     * 将数据库列名转换为 setter 方法名
     * @param columnName 数据库列名（如：soe_id, user_name）
     * @return 对应的 setter 方法名（如：setSoeId, setUserName）
     */
    private static String findSetterName(String columnName) {
        // 处理空值情况
        if (columnName == null || columnName.isEmpty()) {
            throw new IllegalArgumentException("列名不能为空");
        }

        StringBuilder setterName = new StringBuilder("set");

        // 分割下划线并转换驼峰
        String[] parts = columnName.split("_");
        for (String part : parts) {
            if (!part.isEmpty()) {
                // 首字母大写
                setterName.append(part.substring(0, 1).toUpperCase());
                // 其余部分保持原样
                if (part.length() > 1) {
                    setterName.append(part.substring(1));
                }
            }
        }
        return setterName.toString();
    }

    // 小驼峰转下划线
    public static String camelToUnderline(String camelCase) {
        if (camelCase.equals("index")){
            return "\"index\"";
        }
        if (camelCase == null || camelCase.isEmpty()) {
            return camelCase;
        }

        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < camelCase.length(); i++) {
            char c = camelCase.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append('_').append(Character.toLowerCase(c));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }



}